<?php

class Model_Place {

    private static $MAX_PRIORITY = 10;
    private static $MIN_PRIORITY = 0;

    public function getAllPlace() {
        $sql = "Select id,name,avatarLink,addressId,phoneNumber,priority from place order by priority desc";
        $db = Model_Connections::getInstance()->getDbConnect();
        $data = $db->fetchAll($sql);
        return $data;
    }

    public function updatePriority($id, $pri) {
        if ($id != null && $pri != null) {
            $id = mysql_real_escape_string($id);
            $pri = mysql_real_escape_string($pri);

            if ($pri >= self::$MIN_PRIORITY && $pri <= self::$MAX_PRIORITY) {
                $sql = "UPDATE `place` SET `priority`=" . $pri . " WHERE `id` = " . $id;
                $db = Model_Connections::getInstance()->getDbConnect();
                $data = $db->query($sql);
                return true;
            }
        }
        return null;
    }
    
    public function api_searchPlaceByStreetName($streetName, $page, $count){
        if ($streetName != null && $page != null && $page >= 1 && $count != null && $count >= 1) {
            $streetName = mysql_real_escape_string($streetName);
            $sql = 'SELECT p.id, p.name AS "Name", p.avatarLink AS "PictureUrl",a.info as "info", a.number AS "Number", a.street AS "Street", a.ward AS "Ward", s.name AS "District", p.priority as "Priority", p.datetime as "Timestamp"
                    FROM address a, place p,site s
                    WHERE p.addressId = a.id
                    AND a.site = s.id
                    AND a.street LIKE N\'%' . $streetName . '%\'';

            $start = $count * ($page - 1);
            $end = $page * $count;
            $sql .= ' LIMIT ' . $start . ',' . $end;

            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->fetchAll($sql);
            return $data;
        }
        return null;
    }
    
    public function api_searchPlaceByFoodName($typeName, $page, $count){
         if ($typeName != null && $page != null && $page >= 1 && $count != null && $count >= 1) {
            $typeName = mysql_real_escape_string($typeName);
            $sql = 'SELECT p.id, p.name AS "Name", p.avatarLink AS "PictureUrl",a.info as "info", a.number AS "Number", a.street AS "Street", a.ward AS "Ward", s.name AS "District", p.priority as "Priority", p.datetime as "Timestamp"
                    FROM address a, place p,site s,type t
                    WHERE p.addressId = a.id
                    AND a.site = s.id
                    AND p.type_id = t.id
                    AND t.name LIKE N\'%' . $typeName . '%\'';

            $start = $count * ($page - 1);
            $end = $page * $count;
            $sql .= ' LIMIT ' . $start . ',' . $end;

            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->fetchAll($sql);
            return $data;
        }
        return null;
    }

    public function updateAvatar($id, $avar) {
        if ($id != null && $avar != null) {
            $id = mysql_real_escape_string($id);
            $avar = mysql_real_escape_string($avar);
            $sql = "UPDATE `place` SET `avatarLink`='" . $avar . "' WHERE `id` = " . $id;
            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->query($sql);
            return true;
        }
        return null;
    }

    public function api_getPlaceByBlockId($blockId, $page, $count) {

        if ($blockId != null && $page != null && $count != null) {
            $blockId = mysql_real_escape_string($blockId);
            $page = mysql_real_escape_string($page);
            $count = mysql_real_escape_string($count);
            
            $sql = 'SELECT p.id, p.name AS "Name", p.avatarLink AS "PictureUrl", a.number AS "Number",a.info as "info", a.street AS "Street", a.ward AS "Ward", s.name AS "District", p.priority AS "Priority", p.datetime AS "Timestamp"
                    FROM address a, Place p, Site s
                    WHERE a.blockId = ' . $blockId . '
                    AND a.id = p.addressId
                    AND a.site = s.id
                    GROUP BY a.id
                    ORDER BY p.priority DESC';

            $start = $count * ($page - 1);
            $end = $page * $count;
            $sql .= ' LIMIT ' . $start . ',' . $end;

            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->fetchAll($sql);
            return $data;
        }
    }

    public function api_getPlaceBySiteId($siteId, $page, $count) {
        if ($siteId != null && $page != null && $page >= 1 && $count != null && $count >= 1) {
            $siteId = mysql_real_escape_string($siteId);

            $sql = 'SELECT  p.id, p.name as "Name", p.avatarLink as "PictureUrl",a.info as "info",a.number as "Number",a.street as "Street",
                            a.ward as "Ward" ,s.name as "District",p.priority as "Priority", p.datetime as "Timestamp"
                    FROM address a, place p,site s
                    WHERE p.addressId = a.id
                    AND a.site = s.id
                    AND a.site = ' . $siteId;

            $start = $count * ($page - 1);
            $end = $page * $count;
            $sql .= ' LIMIT ' . $start . ',' . $end;

            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->fetchAll($sql);
            return $data;
        }
        return null;
    }

    public function api_searchPlaceByPlaceName($placeName, $page, $count) {
        if ($placeName != null && $page != null && $page >= 1 && $count != null && $count >= 1) {
            $placeName = mysql_real_escape_string($placeName);
            $sql = 'SELECT p.id, p.name AS "Name", p.avatarLink AS "PictureUrl",a.info as "info", a.number AS "Number", a.street AS "Street", a.ward AS "Ward", s.name AS "District", p.priority as "Priority", p.datetime as "Timestamp"
                    FROM address a, place p,site s
                    WHERE p.addressId = a.id
                    AND a.site = s.id
                    AND p.name LIKE N\'%' . $placeName . '%\'';

            $start = $count * ($page - 1);
            $end = $page * $count;
            $sql .= ' LIMIT ' . $start . ',' . $end;

            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->fetchAll($sql);
            return $data;
        }
        return null;
    }

    public function api_searchPlaceByLocation($lon, $lat, $page, $count) {
        if ($lon != null && $lat != null && $page >= 1 && $count != null && $count >= 1) {
            $db = Model_Connections::getInstance()->getDbConnect();

            $model = new Model_Block();
            $blockId = $model->getBlockId($lon, $lat);

            if ($blockId == -1 || $blockId == null)
                return null;
            else {
                $queryBlock = 'SELECT  p.id, p.name as "Name",a.info as "info", p.avatarLink as "PictureUrl",a.number as "Number",a.street as "Street",
                            a.ward as "Ward" ,s.name as "District",p.priority as "Priority", p.datetime as "Timestamp"
                    FROM address a, place p,site s,block b
                    WHERE p.addressId = a.id
                    AND a.site = s.id
                    AND b.id = a.blockId
                    AND a.blockId = ' . $blockId;
                $start = $count * ($page - 1);
                $end = $page * $count;
                $queryBlock .= ' LIMIT ' . $start . ',' . $end;
                $db = Model_Connections::getInstance()->getDbConnect();
                $data = $db->fetchAll($queryBlock);
                return $data;
            }
        }
        return null;
    }

    public function api_getPlaceByTypeId($typeId, $page, $count) {
        if ($typeId != null && $page != null && $page >= 1 && $count != null && $count >= 1) {
            $typeId = mysql_real_escape_string($typeId);
            $sql = 'SELECT  p.id, p.name as "Name", p.avatarLink as "PictureUrl",a.info as "info",a.number as "Number",a.street as "Street",
                            a.ward as "Ward" , s.name as "District",p.priority as "Priority", p.datetime as "Timestamp"
                    FROM place p,address a, site s, type t
                    Where p.type_id = t.id
                    and p.addressId = a.id
                    and p.type_id = ' . $typeId . '
                    group by p.id';
            $start = $count * ($page - 1);
            $end = $page * $count;
            $sql .= ' LIMIT ' . $start . ',' . $end;
            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->fetchAll($sql);
            return $data;
        }
        return null;
    }

    public function api_getPlaceDetailById($id) {
        if ($id != null) {
            $id = mysql_real_escape_string($id);
            $sql = 'select  p.id, p.avatarLink as "PictureUrl", p.name as "Name", p.phoneNumber as "PhoneNumber", 
                            p.`mapImageId` as "MapId", p.`reviewId` as "ReviewId",p.`menuId` as "MenuId", a.info as "info",
                            a.number as "Number",a.street as "Street",a.lon as "Longitdue",a.lat as "Latitude" ,a.ward as "Ward", s.name as "District", p.datetime as "Timestamp"
                    From place p,address a,site s
                    Where p.addressId = a.id
                    and a.site = s.id
                    and p.id = ' . $id;
            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->fetchAll($sql);
            if (!empty($data))
                return $data[0];
            return null;
        }
        return null;
    }

    public function getPlaceById($id) {
        if (isset($id) && $id != null) {
            $sql = sprintf("Select * from place where id = '%s'", mysql_real_escape_string($id));
            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->fetchAll($sql);
            return $data;
        }
        return null;
    }

    public function updateTypeOfPlace($id, $type) {
        if (isset($id) && $id != null && isset($type) && $type != null) {
            $id = mysql_real_escape_string($id);
            $type = mysql_real_escape_string($type);
            $sql = 'UPDATE `place` SET `type_id` = ' . $type . ' WHERE `id` = ' . $id;

            $db = Model_Connections::getInstance()->getDbConnect();
            return $db->query($sql);
        }
        return null;
    }

    public function updatePhoneAndNamePlace($id, $name, $phonenumber) {
        if (isset($id) && $id != null && isset($name) && $name != null && isset($phonenumber) && $phonenumber != null) {
            $id = mysql_real_escape_string($id);
            $name = mysql_real_escape_string($name);
            $phonenumber = mysql_real_escape_string($phonenumber);
            $sql = "UPDATE `place` SET `name` = N'" . $name . " ', `phoneNumber` = '" . $phonenumber . "' WHERE `id` = '" . $id . "'";
            $db = Model_Connections::getInstance()->getDbConnect();
            return $db->query($sql);
        }
        return null;
    }

    public function getPlaceDetailInfoById($id) {
        if ($id != null) {
            $data = array();
            $id = mysql_real_escape_string($id);
            $rs = $this->getPlaceById($id);
            if ($rs != null && isset($rs[0])) {
                $rs = $rs[0];
                $data['place'] = $rs;

                $addressId = $rs['addressId'];
                $model = new Model_Address();
                $addressId = $model->getAddressAllById($addressId);
                if ($addressId != null) {
                    $data['address'] = $addressId[0];
                }

                $menuId = $rs['menuId'];
                $model = new Model_Menu();
                $menuId = $model->getMenuAllById($menuId);
                if ($menuId != null) {
                    $data['menu'] = $menuId[0];
                }

                $reviewId = $rs['reviewId'];
                $model = new Model_Review();
                $reviewId = $model->getReviewAllById($reviewId);
                if ($reviewId != null) {
                    $data['review'] = $reviewId[0];
                }

                $mapImageId = $rs['mapImageId'];
                $model = new Model_Map();
                $mapImageId = $model->getMapImageAllById($mapImageId);
                if ($mapImageId != null) {
                    $data['map'] = $mapImageId[0];
                }

                $typeId = $rs['type_id'];
                $model = new Model_Type();
                $typeId = $model->getTypeById($typeId);
                if ($mapImageId != null) {
                    $data['type'] = $typeId[0];
                }
                $data['alltype'] = $model->getAllType();

                $model = new Model_Site();
                $data['allSite'] = $model->getAllSite();

                return $data;
            }
        }
        return null;
    }

    public function updatePlace($id) {
        
    }

    public function deletePlace($id) {
        if (isset($id) && $id != null) {
            $place = $this->getPlaceById($id);

            if ($place != null) {
                $place = $place[0];
                $addressId = $place["addressId"];
                $modelAddr = new Model_Address();
                $modelAddr->deleteAddress($addressId);

                $menuId = $place["menuId"];
                $modelMen = new Model_Menu();
                $modelMen->deleteMenu($menuId);

                $reviewId = $place["reviewId"];
                $modelRev = new Model_Review();
                $modelRev->deleteReview($reviewId);

                $mapImageId = $place["mapImageId"];
                $model = new Model_Map();
                $model->deleteMap($mapImageId);

                $sql = sprintf("DELETE FROM `place` WHERE id = '%s' ", mysql_real_escape_string($id));
                $db = Model_Connections::getInstance()->getDbConnect();
                $data = $db->query($sql);
                return true;
            }
            return false;
        }
    }

    public function insertPlace($name, $addressId, $avatarLink, $menuId, $phoneNumber, $reviewId, $mapImageId, $type) {
        if (isset($name) && isset($addressId)
                && isset($menuId) && isset($phoneNumber)
                && isset($reviewId) && isset($mapImageId)
                && isset($type)) {
            $query = sprintf("INSERT INTO `place`(`name`,`avatarLink`, `addressId`, `menuId`, `phoneNumber`, `reviewId`, `mapImageId`, `type_id`) 
                VALUES (N'%s','%s','%s','%s','%s','%s','%s','%s')"
                    , mysql_real_escape_string($name)
                    , mysql_real_escape_string($avatarLink)
                    , mysql_real_escape_string($addressId)
                    , mysql_real_escape_string($menuId)
                    , mysql_real_escape_string($phoneNumber)
                    , mysql_real_escape_string($reviewId)
                    , mysql_real_escape_string($mapImageId)
                    , mysql_real_escape_string($type));
            $db = Model_Connections::getInstance()->getDbConnect();
            $data = $db->query($query);
            $query = "Select Max(id) from place";
            $id = $db->fetchAll($query);
            if ($id != null)
                return $id[0]['Max(id)'];
            return null;
        }
    }

}
?>

